IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLink_GetRecentLinks]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLink_GetRecentLinks]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLink_GetPopularLinks]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLink_GetPopularLinks]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[cs_ResLinkGetLinksBase]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cs_ResLinkGetLinksBase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.cs_ResLink_GetPopularLinks
(
	@Top int,
	@SectionID int,
	@SettingsID int
)
AS

SET NOCOUNT ON 
			
create TABLE #LinksTable  
(
	SectionID int, 
	PostID int,
	ThreadID int
) 

insert into #LinksTable
	exec dbo.cs_ResLinkGetLinksBase @SettingsID , @SectionID , 0 , 0 , 1

SET ROWCOUNT @Top
	
SELECT Tbl.SectionID , Tbl.PostID from #LinksTable as Tbl
	JOIN cs_Sections  ON Tbl.SectionID = cs_Sections.SectionID
	JOIN cs_Posts  ON cs_Posts.PostID = Tbl.PostID
	where IsApproved =1 
			and cs_Sections.IsActive = 1 and cs_Sections.ApplicationType = 100
			and cs_Sections.SettingsID = @SettingsID Order by cs_Posts.TotalViews desc
			
SET ROWCOUNT 0

RETURN 

GRANT EXECUTE ON [dbo].cs_ResLink_GetPopularLinks TO [public]
GO

CREATE PROCEDURE dbo.cs_ResLink_GetRecentLinks
(
	@Top int,
	@SectionID int,
	@SettingsID int
)
AS

SET NOCOUNT ON 
			
create TABLE #LinksTable  
(
	SectionID int, 
	PostID int,
	ThreadID int
) 	

insert into #LinksTable 
	exec dbo.cs_ResLinkGetLinksBase @SettingsID , @SectionID , 0 , 0 , 1

SET ROWCOUNT @Top
	
SELECT Tbl.SectionID , Tbl.PostID from #LinksTable as Tbl
	JOIN cs_Sections  ON Tbl.SectionID = cs_Sections.SectionID
	JOIN cs_Posts  ON cs_Posts.PostID = Tbl.PostID
	where PostDate > DATEADD(day , -7 , GETDATE()) and IsApproved =1 
			and cs_Sections.IsActive = 1 and cs_Sections.ApplicationType = 100
			and cs_Sections.SettingsID = @SettingsID Order by cs_Posts.PostDate desc

drop table 	#LinksTable
		
SET ROWCOUNT 0

RETURN 

GRANT EXECUTE ON [dbo].[cs_ResLink_GetRecentLinks] TO [public]
GO


CREATE PROCEDURE dbo.cs_ResLinkGetLinksBase
(
	@SettingsID int,
	@SectionID int,
	@ViewUnmoderated bit,
	@ViewDead bit,
	@PromoteLinks bit = 0	
)

AS
declare @queryString varchar(4000)

set @queryString = 'SELECT 	Posts.SectionID , Posts.PostID  , Posts.ThreadID FROM cs_Posts Posts 
						JOIN cs_ResourceLinks_Post LinkedTbl ON  LinkedTbl.PostID = Posts.PostID'
	
if @PromoteLinks = 1
	set @queryString = @queryString + ' Where Posts.SectionID IN (SELECT SectionID FROM dbo.fn_SectionAndDescendants('+ cast(@SectionID as varchar) +')) and Posts.SettingsID = '+ cast(@SettingsID as varchar) +' AND ( '+ cast(@ViewDead as varchar) +' = ReportedDead OR ReportedDead = 0) AND PostType=0'
else
	set @queryString = @queryString + ' Where Posts.SectionID = '+ cast(@SectionID as varchar) +' and Posts.SettingsID = '+ cast(@SettingsID as varchar) +'  AND ( '+ cast(@ViewDead as varchar) +' = ReportedDead OR ReportedDead = 0) AND PostType=0'

if @ViewUnmoderated = 0
	set @queryString = @queryString + ' and Posts.IsApproved = 1 '

exec (@queryString)

RETURN 

GRANT EXECUTE ON [dbo].[cs_ResLinkGetLinksBase] TO [public]
GO

ALTER Procedure dbo.cs_ResLinkSection_Get
(
	@SettingsID int,
	@SectionID int,
	@ApplicationType smallint
)

as
SET Transaction Isolation Level Read UNCOMMITTED

IF @SectionID < 0 
BEGIN
	/* Get this sections details */
	SELECT 
		@SectionID  = SectionID
	FROM 
		cs_Sections 
	Where ParentID = 0 and ApplicationType = @ApplicationType and SettingsID = @SettingsID 	
	
	set @SectionID = 0
END

/* Get this sections details */
SELECT 
	SectionID, SettingsID, ParentID, [Name],  [Description] , ApplicationKey ,PropertyNames , PropertyValues , IsActive , IsModerated , ApplicationType
FROM 
	cs_Sections 
Where SectionID = @SectionID and ApplicationType = @ApplicationType and SettingsID = @SettingsID 


/*Get this sections security*/
exec cs_Section_Permissions_Get @SettingsID  ,  @SectionID , @ApplicationType

/*Now select the child sections - lite*/
SELECT 
	SectionID  ,[Name] , IsActive ,  ApplicationKey 
FROM 
	cs_Sections 
Where ParentID = @SectionID and ApplicationType = @ApplicationType and SettingsID = @SettingsID 


--Get the number of links in this suction and all sub sections
select count(Posts.PostID) as LinkCount from cs_Posts Posts
		JOIN cs_ResourceLinks_Post LinkedTbl ON  LinkedTbl.PostID = Posts.PostID 
		Where Posts.SectionID IN (SELECT SectionID FROM dbo.fn_SectionAndDescendants(@SectionID)) and Posts.SettingsID = @SettingsID AND ( 1 = ReportedDead OR ReportedDead = 0) AND PostType=0

GO

ALTER Procedure dbo.cs_ResLinkSection_GetLinks
(
	@SettingsID int,
	@SectionID int,
	@ViewUnmoderated bit,
	@ViewDead bit,
	@PromoteLinks bit = 0,
	@Page int,
	@PageCount int = -1,
	@SortOrder int =0
)

as
SET Transaction Isolation Level Read UNCOMMITTED

Create TABLE #LinksTable 
(
	IDX	int IDENTITY,
	SectionID int, 
	PostID int,
	ThreadID int
) 	

insert into #LinksTable 
	exec dbo.cs_ResLinkGetLinksBase @SettingsID , @SectionID , @ViewUnmoderated , @ViewDead , @PromoteLinks

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int

if (@PageCount > -1 )
BEGIN
	SELECT @FirstRec = (@Page - 1) * @PageCount
	SELECT @LastRec = (@Page * @PageCount + 1)
END
ELSE
BEGIN 
	SELECT @FirstRec = 0
	SELECT @LastRec = count(*)+1 FROM #LinksTable 
END

--THis is bad must find a better way or the SP will come to a grinding halt
declare @resultsTable TABLE
(
	IDX int IDENTITY, SectionID int, PostID  int, 
	UserID int, TotalViews int, ThreadID int , Subject nvarchar(256), Body  nText , PostAuthor nvarchar(64), PostDate datetime, FormattedBody ntext, IsApproved bit, LinkURL nvarchar(255), ReportedDead bit , ReportingUserID int ,
	PropertyNames ntext, PropertyValues nText, MostRecentPostAuthor nvarchar(64), MostRecentPostAuthorID int,  RatingSum int, TotalRatings int,
	CommentCount int, 
	CommentModerationCount int
)

if @ViewUnmoderated = 0
begin

INSERT INTO @resultsTable (SectionID , PostID  , 
	UserID , TotalViews , ThreadID  , Subject , Body  , PostAuthor , PostDate , FormattedBody , IsApproved , LinkURL , ReportedDead  , ReportingUserID  ,
	PropertyNames , PropertyValues , MostRecentPostAuthor , MostRecentPostAuthorID ,  RatingSum , TotalRatings ,
	CommentCount , 
	CommentModerationCount)
SELECT  Lnk.SectionID , Lnk.PostID  , 
		Posts.UserID , Posts.TotalViews , Posts.ThreadID , Posts.Subject , Posts.Body , Posts.PostAuthor , Posts.PostDate , Posts.FormattedBody , Posts.IsApproved , LinkedTbl.LinkURL , LinkedTbl.ReportedDead , LinkedTbl.ReportingUserID ,
		Posts.PropertyNames , Posts.PropertyValues , Thread.MostRecentPostAuthor , Thread.MostRecentPostAuthorID , Thread.RatingSum , Thread.TotalRatings,
		(SELECT Count(*) FROM cs_Posts Posts2 WHERE Posts2.ParentID = Posts.PostID  AND Posts2.IsApproved=1 ) as CommentCount, 
		(SELECT Count(*) FROM cs_Posts Posts3 WHERE Posts3.ParentID = Posts.PostID AND Posts3.IsApproved=0) as CommentModerationCount
from #LinksTable  as Lnk
	JOIN cs_Threads Thread ON Thread.ThreadID = Lnk.ThreadID
	JOIN cs_Posts Posts ON Posts.PostID = Lnk.PostID 
	JOIN cs_ResourceLinks_Post LinkedTbl ON  LinkedTbl.PostID = Posts.PostID
WHERE Posts.IsApproved = 1 
ORDER BY CASE WHEN @SortOrder = 2 THEN Posts.PostDate END DESC, 
		 CASE WHEN @SortOrder = 1 THEN Posts.TotalViews END DESC,
         CASE WHEN @SortOrder = 3 THEN CASE WHEN Thread.TotalRatings > 0 THEN Thread.RatingSum / Thread.TotalRatings ELSE 0 END END DESC,
		 CASE WHEN @SortOrder = 0 THEN Posts.Subject END DESC           	
	
end
else
begin


INSERT INTO @resultsTable (SectionID , PostID  , 
	UserID , TotalViews , ThreadID  , Subject , Body  , PostAuthor , PostDate , FormattedBody , IsApproved , LinkURL , ReportedDead  , ReportingUserID  ,
	PropertyNames , PropertyValues , MostRecentPostAuthor , MostRecentPostAuthorID ,  RatingSum , TotalRatings ,
	CommentCount , 
	CommentModerationCount)
SELECT Lnk.SectionID , Lnk.PostID  , 
		Posts.UserID , Posts.TotalViews , Posts.ThreadID , Posts.Subject , Posts.Body , Posts.PostAuthor , Posts.PostDate , Posts.FormattedBody , Posts.IsApproved , LinkedTbl.LinkURL , LinkedTbl.ReportedDead , LinkedTbl.ReportingUserID ,
		Posts.PropertyNames , Posts.PropertyValues , Thread.MostRecentPostAuthor , Thread.MostRecentPostAuthorID , Thread.RatingSum , Thread.TotalRatings,
		(SELECT Count(*) FROM cs_Posts Posts2 WHERE Posts2.ParentID = Posts.PostID  AND Posts2.IsApproved=1 ) as CommentCount, 
		(SELECT Count(*) FROM cs_Posts Posts3 WHERE Posts3.ParentID = Posts.PostID) as CommentModerationCount
from #LinksTable  as Lnk
	JOIN cs_Threads Thread ON Thread.ThreadID = Lnk.ThreadID
	JOIN cs_Posts Posts ON Posts.PostID = Lnk.PostID 
	JOIN cs_ResourceLinks_Post LinkedTbl ON  LinkedTbl.PostID = Posts.PostID
ORDER BY CASE WHEN @SortOrder = 2 THEN Posts.PostDate END DESC, 
		 CASE WHEN @SortOrder = 1 THEN Posts.TotalViews END DESC,
         CASE WHEN @SortOrder = 3 THEN CASE WHEN Thread.TotalRatings > 0 THEN Thread.RatingSum / Thread.TotalRatings ELSE 0 END END DESC,
		 CASE WHEN @SortOrder = 0 THEN Posts.Subject END DESC
        
end

SELECT * FROM @resultsTable WHERE IDX > @FirstRec AND IDX < @LastRec  order by IDX	 

SELECT COUNT(*) as MoreRecords FROM #LinksTable TI WHERE TI.IDX >= @LastRec

Return
GO

CREATE FUNCTION [dbo].[fn_SectionAndDescendants] (@section int) RETURNS @descendants TABLE
                   (SectionID int NOT NULL PRIMARY KEY,
                    PathLength int NOT NULL CHECK (PathLength >= 0)) AS BEGIN DECLARE @path_length int, @next_path_length int IF EXISTS (SELECT * FROM cs_Sections WHERE SectionId = @section or ParentID=0) BEGIN
   SET @path_length = 0
   SET @next_path_length = 1
   INSERT INTO @descendants (SectionID, PathLength)
   VALUES (@section, @path_length)
END
WHILE EXISTS (SELECT * FROM @descendants
                             WHERE PathLength = @path_length) BEGIN
   INSERT INTO @descendants (SectionID, PathLength)
   SELECT S.SectionID, @next_path_length
   FROM @descendants AS D
               INNER JOIN
               cs_Sections AS S
               ON D.PathLength = @path_length AND
                      D.SectionID = S.ParentID
   SET @path_length = @next_path_length
   SET @next_path_length = @next_path_length + 1 END RETURN END
GO

